﻿/* Co so du lieu quan ly cua hang trang tri noi that
Date: 06/03/2013

*/
--> Tao CSDL quanlicuahanganuong
USE MASTER
GO

IF EXISTS (SELECT * FROM SYSDATABASES WHERE NAME = 'quanlicuahanganuong')
DROP DATABASE quanlicuahanganuong
GO

CREATE DATABASE quanlicuahanganuong
GO

--> - Thiet lap kieu ngay thang nam.
SET DATEFORMAT dmy
GO

--> - Dung ALXKHI, tao cac bang du lieu.
USE quanlicuahanganuong
GO


-->14. TAI_KHOAN
CREATE TABLE TAIKHOAN
(
	USERID 		VARCHAR(20)	 NOT NULL,
	PASSID			VARCHAR(50) NOT NULL,
	QUYEN_TRUY_CAP		NVARCHAR(20),
	PRIMARY KEY (USERID),
)
INSERT INTO TAIKHOAN VALUES('admin', 'admin', 'admin')

GO

CREATE TABLE KHUVUC
(
	MAKV 		VARCHAR(8)	 NOT NULL,
	TENKV			NVARCHAR(30) NOT NULL,
	PRIMARY KEY (MAKV),
)

INSERT INTO KHUVUC VALUES ('kv01',N'khu vực 1')

GO

CREATE TABLE LOAIMON
(
	MALOAI VARCHAR(8) NOT NULL,
	TENLOAI NVARCHAR(20),
	PRIMARY KEY(MALOAI),
)
INSERT INTO LOAIMON VALUES('LM01',N'kem')
INSERT INTO LOAIMON VALUES('LM02',N'sữa')
INSERT INTO LOAIMON VALUES('LM03',N'cafe')



GO

CREATE TABLE THUCDON
(
	MAMON VARCHAR(8) NOT NULL,
	TENMON NVARCHAR(20),
	MALOAI VARCHAR(8) CONSTRAINT FK_LOAI FOREIGN KEY REFERENCES LOAIMON(MALOAI),
	DONGIA	DECIMAL(9,2),
	DVT NVARCHAR(10),
	PRIMARY KEY(MAMON),
)
insert into  THUCDON values('KDAU',N'kem dâu0','LM01','10000','ly')
insert into  THUCDON values('KDAU1',N'kem dâu1','LM01','10000','ly')
insert into  THUCDON values('KDAU2',N'kem dâu2','LM01','10000','ly')
insert into  THUCDON values('KDAU3',N'kem dâu3','LM01','10000','ly')

insert into  THUCDON values('KDAU4',N'kem dâu4','LM01','10000','ly')

insert into  THUCDON values('KDAU8',N'kem dâu1','LM02','10000','ly')
insert into  THUCDON values('KDAU5',N'kem dâu2','LM02','10000','ly')
insert into  THUCDON values('KDAU6',N'kem dâu3','LM02','10000','ly')

insert into  THUCDON values('KDAU7',N'kem dâu4','LM02','10000','ly')

GO

CREATE TABLE BAN
(
	MABAN 		VARCHAR(8)	 NOT NULL,
	TENBAN			NVARCHAR(20) NOT NULL,
	MAKV		VARCHAR(8)
	CONSTRAINT FK_NHAN_VIEN_CHUC_VU FOREIGN KEY REFERENCES KHUVUC(MAKV), 
	PRIMARY KEY (MABAN),
)
insert into BAN values('BN01',N'Bàn 01','kv01');
insert into BAN values('BN02',N'Bàn 02','kv01');
insert into BAN values('BN03',N'Bàn 03','kv01');
insert into BAN values('BN04',N'Bàn 04','kv01');
insert into BAN values('BN05',N'Bàn 05','kv01');
insert into BAN values('BN06',N'Bàn 06','kv01');

insert into BAN values('BN07',N'Bàn 07','kv01');
insert into BAN values('BN08',N'Bàn 08','kv01');
insert into BAN values('BN09',N'Bàn 09','kv01');
insert into BAN values('BN010',N'Bàn 10','kv01');
insert into BAN values('BN011',N'Bàn 11','kv01');
insert into BAN values('BN012',N'Bàn 12','kv01');

GO

--> 1.CHUC VU
--CREATE TABLE CHUC_VU
--(
--	MA_CV	CHAR    (4)	NOT NULL,
--	TEN_CV	NVARCHAR (30)	NULL,
--	CONSTRAINT PK_CHUC_VU PRIMARY KEY (MA_CV)
--)
--
--GO

--> 2.NHA CUNG CAP




--> 6.NHAN VIEN
CREATE TABLE NHANVIEN
(
	MANV		VARCHAR(8)	NOT NULL,
	HOTEN	NVARCHAR (50)	NULL,
	--TEN_DANG_NHAP VARCHAR(20) CONSTRAINT FK_NHAN_VIEN_TAI_KHOAN FOREIGN KEY REFERENCES TAI_KHOAN(TEN_DANG_NHAP),
	--MA_CV		CHAR(4) CONSTRAINT FK_NHAN_VIEN_CHUC_VU FOREIGN KEY REFERENCES CHUC_VU(MA_CV), 
	--GIOITINH	NVARCHAR(5),
	DIACHI		NVARCHAR (50)	NULL,
	DIENTHOAI		VARCHAR (13)	NULL,
	NGAYVAOLAM	DATETIME			NULL,
	SONGAYLAM	INT ,
	LUONG		DECIMAL(9,2),
	PHAI		NVARCHAR(5),
	NGAYSINH	DATETIME,
	CA			NVARCHAR(10),
	CONSTRAINT PK_NHAN_VIEN PRIMARY KEY (MANV)
)

insert into NHANVIEN values('THP',N'trần hoài phong',N'bến tre','0932885502','20/11/2012','30','1500000','Nam','11/11/1988',N'Sáng')

GO

--> 8. PHIEU DAT HANG
CREATE TABLE PHIEU
(
	MAPHIEU			VARCHAR(8) NOT NULL,
	NGAYTAOPHIEU	DATETIME ,
	TINHTIEN	DECIMAL(9,2) ,
	MABAN			VARCHAR(8)
	CONSTRAINT FK_BAN FOREIGN KEY REFERENCES BAN(MABAN),
	PRIMARY KEY(MAPHIEU),
	--TONG_TIEN			INT		    NOT NULL,

)
insert into PHIEU values ('PH01','25/05/2013','10000','BN01')
insert into PHIEU values ('PH02','25/05/2013','10000','BN02')
insert into PHIEU values ('PH03','25/05/2013','10000','BN03')

GO

--> 9. CHI_TIET_DAT_HANG
CREATE TABLE CHITIETPHIEU
(
	MAPHIEU			VARCHAR(8)		CONSTRAINT FK_PDHKK FOREIGN KEY REFERENCES PHIEU(MAPHIEU),
	MAMON			VARCHAR(8) CONSTRAINT FK_MA_SP FOREIGN KEY REFERENCES THUCDON(MAMON),
	GIAMGIA     INT,
	DONGIA		DECIMAL(9,3),
	SOLUONG		INT,
	TINHTIEN		DECIMAL(9,3),
	MANV	VARCHAR(8)CONSTRAINT FK_MA FOREIGN KEY REFERENCES NHANVIEN(MANV),
	PRIMARY KEY (MAPHIEU, MAMON)
	
)
INSERT INTO CHITIETPHIEU VALUES ('PH01','Kdau','0','10000','01','10000','THP')
INSERT INTO CHITIETPHIEU VALUES ('PH02','Kdau','0','10000','02','20000','THP')
INSERT INTO CHITIETPHIEU VALUES ('PH03','Kdau','0','10000','01','10000','THP')
INSERT INTO CHITIETPHIEU VALUES ('PH03','kdau2','0','10000','02','20000','THP')






---luu tru phieu

CREATE TABLE SAVING
(
	MAPHIEU			VARCHAR(8) ,
	NGAYTAOPHIEU	DATETIME ,
	TINHTIEN	DECIMAL(9,2) ,
	MABAN			VARCHAR(8),
)


